package common.util.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * 数据库连接池管理
 * (此类使用DataSource进行获取数据库连接)
 *
 * @author Eric.tyc
 * @version 1.0.1 | 2012-06-20
 */
public class ConnectionPool {
	
	private DataSource dataSource = null;
	
	/**
	 * 默认以驱动"com.mysql.jdbc.Driver"，端口3306
	 * 连接MYSQL数据库
	 * 
	 * @param serverIp
	 * @param dbName
	 * @param userName
	 * @param password
	 */
	public ConnectionPool(String serverIp, String dbName, String userName, String password) {
		this(serverIp, 3306, dbName, userName, password, "com.mysql.jdbc.Driver");
	}
	
	public ConnectionPool() {
		this(SysConstant.GAME_DATABASE_SERVER_IP,
				SysConstant.GAME_DATABASE_NAME, 
				SysConstant.GAME_DATABASE_USERNAME, 
				SysConstant.GAME_DATABASE_PASSWORD);
	}
	
	/**
	 * 构造函数
	 * 
	 * @param serverIp
	 * @param serverPort
	 * @param dbName
	 * @param userName
	 * @param password
	 * @param driver
	 */
	public ConnectionPool(String serverIp, int serverPort, String dbName, String userName, String password, String driver) {
		String connectURI = getConnectURI(serverIp, serverPort, dbName, userName, password);
		
		try {
			Class.forName(driver);
			dataSource = setupDataSource(connectURI);
		} catch ( ClassNotFoundException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 设置数据源
	 * 
	 * @param connectURI
	 */
	public DataSource setupDataSource(String connectURI) {
		ObjectPool connectionPool = new GenericObjectPool(null);
		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI, getProp());
		new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
		PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
		return dataSource;
	}
	
	/**
	 * 获得连接
	 * 
	 * @return
	 */
	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch ( SQLException e ) {	
			throw new RuntimeException("can not open database connection!");
		} catch ( Exception e ) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	/**
	 * 获取连接资源定位
	 * 
	 * @param serverIp
	 * @param serverPort
	 * @param dbName
	 * @param userName
	 * @param password
	 * @return
	 */
	public String getConnectURI(String serverIp, int serverPort, String dbName, String userName, String password) {
		String result =  "jdbc:mysql://" + serverIp + ":" + Integer.toString(serverPort) + 
						 "/"+ dbName + "?user=" + userName + "&password=" + password + 
						 "&useUnicode=true&characterEncoding=utf-8"+
						 "&connectTimeout=25200&socketTimeout=25200"+//超时的时间  必须比mysql设置的值低,mysql默认8小时  这里设置7小时
						 "&autoReconnect=true" +//autoReconnect当数据库连接异常中断时，是否自动重新连接(MySQL5中已经无效了)
						 "&maxReconnects=10" +//autoReconnect设置为true时，重试连接的次数
						 "&initialTimeout=1";//autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒
		return result ;
	}
	
	/**
	 * 执行清理过程
	 * 
	 * <li>关闭数据库连接</li> 
	 * <li>关闭语句对象</li> 
	 * <li>关闭结果集</li>
	 * 
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public void closeAll(Connection conn, Statement stmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 设置配置信息
	 * 
	 * @return
	 */
	public Properties getProp() {
		Properties prop = new Properties();
		prop.setProperty("initialSize", "10");
		prop.setProperty("maxActive", "50");
		prop.setProperty("maxIdle", "20");
		prop.setProperty("minIdle", "5");
		prop.setProperty("maxWait", "60000");
		
		return prop;
	}
	
	public static void main(String[] args) throws Exception {
//		Connection conn = null;
//		Statement stmt = null;
//		ResultSet rs = null;
//		
//		ConnectionPool connPool = new ConnectionPool("192.168.2.15", "kom_user_login", "root", "");
//		
//		for ( int n = 0; n <= 10; n++ ) {
//			LinkedList<HashMap<String, Object>> result = new LinkedList<HashMap<String, Object>>();
//			try {
//				conn = connPool.getConnection();
//				stmt = conn.createStatement();
//				rs = stmt.executeQuery("SELECT * FROM `kom_user_account`");
//				if ( rs != null ) {
//					ResultSetMetaData rsmd = rs.getMetaData();
//					int columnCount = rsmd.getColumnCount();
//					while ( rs.next() ) {
//						HashMap<String, Object> data = new HashMap<String, Object>();
//						for ( int i = 1; i <= columnCount; i++ ) {
//							data.put(rsmd.getColumnName(i), rs.getObject(i));
//						}
//						result.add(data);
//					}
//				}
//			} catch (SQLException e) {
//				e.printStackTrace();
//			} catch ( Exception e ) {
//				e.printStackTrace();
//			} finally {
//				connPool.closeAll(conn, stmt, rs);
//			}
//			
//			System.out.println(result);
//		}
	}
}
